<?php

/*
 * Copyright (C) 2006-2009 Pham Cong Dinh
 *
 * This file is part of Pone.
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 3 of
 * the License, or (at your option) any later version.
 *
 * This software is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
 */

/**
 * An object that represents a precompiled SQL statement
 *
 * <P>A SQL statement is precompiled and stored in a <code>SpicaPreparedStatement</code> object
 *
 * This object can then be used to efficiently execute this statement multiple times
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      October 26, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: PreparedStatement.php 1603 2009-12-26 19:55:19Z pcdinh $
 */

/**
 * SQL Statement interface
 */
include_once 'spica/core/datasource/db/Statement.php';

/**
 * Because PHP does not support method overloading, I don't get this interface derived from Statement
 */
interface SpicaPreparedStatement
{
    /**
     * Executes the given SQL statement, which returns a single <code>SpicaResultSet</code> object.
     *
     * @throws SpicaDatabaseException if a database access error occurs, this method is called on a
     *         closed <code>SpicaStatement</code> or the given SQL statement produces
     *         anything other than a single <code>SpicaResultSet</code> object
     * @param  sql an SQL statement to be sent to the database, typically a static SQL <code>SELECT</code> statement
     * @return SpicaResultSet A <code>SpicaResultSet</code> object that contains the data produced
     *         by the given query; never <code>null</code>
     */
    public function executeQuery();

    /**
     * Executes the given SQL statement, which may be an <code>INSERT</code>,
     * <code>UPDATE</code>, or <code>DELETE</code> statement or an
     * SQL statement that returns nothing, such as an SQL DDL statement (CREATE, DROP)
     *
     * @throws SpicaDatabaseException if a database access error occurs,
     * this method is called on a closed <code>SpicaStatement</code> or the given
     *            SQL statement produces a <code>SpicaResultSet</code> object
     * @param string $sql an SQL Data Manipulation Language (DML) statement, such as <code>INSERT</code>,
     *        <code>UPDATE</code> or <code>DELETE</code>; or an SQL statement that returns nothing, such as a DDL statement	 *
     * @return int Either (1) the row count for SQL Data Manipulation Language (DML) statements
     *         or (2) 0 for SQL statements that return nothing
     */
    public function executeUpdate();

    /**
     * Executes the given SQL statement, which may return multiple results.
     * In some (uncommon) situations, a single SQL statement may return multiple result sets and/or update counts.
     *
     * Normally you can ignore this unless you are (1) executing a stored procedure that you know may
     * return multiple results or (2) you are dynamically executing an unknown SQL string.
     * <P>
     * The <code>execute</code> method executes an SQL statement and indicates the
     * form of the first result.  You must then use the methods <code>getResultSet</code> or <code>getUpdateCount</code>
     * to retrieve the result, and <code>getMoreResults</code> to move to any subsequent result(s).
     *
     * @see #getResultSet
     * @see #getUpdateCount
     * @see #getMoreResults
     * @throws SpicaDatabaseException if a database access error occurs or this method is called on a
     *                                       closed <code>SpicaStatement</code>
     * @param  string $sql any SQL statement
     * @return bool <code>true</code> if the first result is a <code>SpicaResultSet</code> object;
     *              <code>false</code> if it is an update count or there are no results
     */
    public function execute();

    /**
     * Adds the given SQL command to the current list of commmands for this Statement object
     *
     */
    public function addBatch();

    /**
     * Submit a batch of commands to the database for execution
     *
     * @return array An array of update counts containing one element for each command
     *         in the batch. The array is ordered according to the order in
     *         which commands were inserted into the batch
     *
     * @throws SpicaDatabaseException
     *                if a database-access error occurs, or the driver does not
     *                support batch statements
     * @throws SpicaDatabaseBatchUpdateException
     */
    public function executeBatch();

    /**
     * Releases this <code>SpicaStatement</code> object's database
     * and Connection resources immediately instead of waiting for
     * this to happen when it is automatically closed.
     * It is generally good practice to release resources as soon as
     * you are finished with them to avoid tying up database
     * resources.
     * <P>
     * Calling the method <code>close</code> on a <code>SpicaStatement</code>
     * object that is already closed has no effect.
     * <P>
     * <B>Note:</B>When a <code>SpicaStatement</code> object is
     * closed, its current <code>SpicaResultSet</code> object, if one exists, is
     * also closed.
     *
     * @throws SpicaDatabaseException if a database access error occurs
     */
    public function close();

    /**
     * Retrieves the current result as a <code>SpicaResultSet</code> object.
     * This method should be called only once per result.
     *
     * @return SpicaResultSet The current result as a <code>SpicaResultSet</code>
     * @throws SpicaDatabaseException if a database access error occurs or
     *         this method is called on a closed <code>SpicaStatement</code>
     * @param  int    $caseMode The value may be one of: null,
     *                          SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                          SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                          SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @see #execute
     */
    public function getResultSet($caseMode = null);

    /**
     * Fetches result set of multiple rows returned from SQL command as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  int    $caseMode The value may be one of: null,
     *                          SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                          SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                          SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array An empty array is returned when there is no row found
     */
    public function fetchAll($caseMode = null);

    /**
     * Fetches a single value of the first row and first column
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @return string|null Null value is returned when no row is found
     */
    public function fetchOne();

    /**
     * Fetches a single column from a result set as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @return array An empty array is returned when there is no row found
     */
    public function fetchColumn();

    /**
     * Fetches a single row from result set returned from SQL command as an associative array
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  int    $caseMode The value may be one of: null,
     *                          SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                          SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                          SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array An empty array is returned when there is no row found
     */
    public function fetchRow($caseMode = null);

    /**
     * Retrieves certain set of rows for page navigation.
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  int    $pageNumber    The page sequence number
     * @param  int    $recordPerPage The number of records that will be displayed on each page
     * @param  int    $caseMode The value may be one of: null,
     *                          SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                          SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                          SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array
     */
    public function fetchPage($pageNumber = 1, $recordPerPage = 20, $caseMode = null);

    /**
     * Retrieves the certain number of rows counted on a given row sequence number.
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  int    $from The beginning row sequence number that will be retrieved, starting with 0
     * @param  int    $to   The last row sequence number that will be retrieved
     * @param  int    $caseMode The value may be one of: null,
     *                          SpicaResultSet::IDENTIFIER_LOWERCASE,
     *                          SpicaResultSet::IDENTIFIER_UPPERCASE,
     *                          SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return array
     */
    public function fetchRange($from = 0, $to = 20, $caseMode = null);

    /**
     * Convenient method to insert a single or multiple records into a table, using an array as user input
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $tableName
     * @param  array $data An array of user input with specification: [field => value]
     * @return int An integer greater than zero indicates the number of rows affected or retrieved
     */
    public function insert($tableName, $data);

    /**
     * Convenient method to update data in a table, using an array as user input
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $tableName
     * @param  array  $data An array of user input with specification: [field => value]
     * @param  string $where Conditional phrase for UPDATE SQL command
     * @return int An integer greater than zero indicates the number of rows affected or retrieved.
     *             Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause
     *             in the query or that no query has yet been executed
     */
    public function update($tableName, $data, $where);

    /**
     * Convenient method to alter an entire record or multiple records based on an unique index (with single or multiple keys)
     * or a primary key, using an array as user input
     *
     * Using REPLACE INTO to insert a record will keep you from inserting duplicate records into your table,
     * providing they have a unique index or a primary key
     *
     * If the table you are replacing into does not have either a unique index or a primary key, then
     * REPLACE INTO becomes the equivalent of an INSERT command, and it makes no sense to use it
     *
     * The REPLACE INTO command works in one of two ways:
     * + It inserts data into a specified record.
     * + If the data to be inserted violates key uniqueness, it deletes the existing record first and then
     * inserts the replacement record.
     *
     * @throws SpicaDatabaseException when database connection is closed or SQL command execution fails
     * @param  string $tableName
     * @param  array  $data An array of user input with specification: [field => value]
     * @return int An integer greater than zero indicates the number of rows affected or retrieved.
     *             Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause
     *             in the query or that no query has yet been executed
     */
    public function replace($tableName, $data);

    /**
     * Convenient method to delete a single row or multiple rows in a table, using an array as user input conditions
     *
     * @throws SpicaDatabaseException when database connection is closed, read-only or SQL command execution fails
     * @param  string $tableName
     * @param  array|string $where Conditional phrase for DELETE SQL command.
     *                      As an arrray: [field => condition]. E.x : array('id' => null, 'name' = 'LIKE %pcdinh%')
     *                      As a string: "id = 3 AND username = 'pcdinh'" (without WHERE)
     * @return int An integer greater than zero indicates the number of rows affected or retrieved.
     *             Zero indicates that no records where updated for an DELETE statement, no rows matched the WHERE clause
     *             in the query or that no query has yet been executed
     */
    public function delete($tableName, $where = array());

    /**
     * Binds named parameters to their values
     *
     * @param array $namedParamValues array of bind values
     */
    public function bindParams($params);

    /**
     * Binds values to prepared SQL statement using a given array of positional/numbered bind variable values
     * as reference for the bind variable order
     *
     * @param array $value array of bind values. E.x: array($name, array('type' => 'int', 'value' => $age), $address)
     */
    public function bindValues($values);
}
?>